/*============================================================================
  File:     agent_sample_alerts.sql

  Summary:  Create default alerts in Microsoft SQL Server 2005.

  Date:     March 03, 2005
------------------------------------------------------------------------------
  This file is part of the Microsoft SQL Server Code Samples.

  Copyright (C) Microsoft Corporation.  All rights reserved.

  This source code is intended only as a supplement to Microsoft
  Development Tools and/or on-line documentation.  See these other
  materials for detailed information regarding Microsoft code samples.

  THIS CODE AND INFORMATION ARE PROVIDED "AS IS" WITHOUT WARRANTY OF ANY
  KIND, EITHER EXPRESSED OR IMPLIED, INCLUDING BUT NOT LIMITED TO THE
  IMPLIED WARRANTIES OF MERCHANTABILITY AND/OR FITNESS FOR A
  PARTICULAR PURPOSE.
============================================================================*/

PRINT ''
PRINT 'Installing default alerts...'
GO

IF (NOT EXISTS (SELECT *
                FROM msdb.dbo.sysalerts
                WHERE (name = N'Sample: Full msdb log')
                   OR ((severity = 0) AND
                       (message_id = 9002) AND
                       (database_name = N'msdb') AND
                       (event_description_keyword IS NULL) AND
                       (performance_condition IS NULL))))
	EXECUTE msdb.dbo.sp_add_alert 
		@name = N'Sample: Full msdb log',
		@message_id = 9002,
		@severity = 0,
		@enabled = 1,
		@delay_between_responses = 10,
		@database_name = N'msdb',
		@notification_message = NULL,
		@job_name = NULL,
		@event_description_keyword = NULL,
		@include_event_description_in = 5; -- Email and NetSend
GO

IF (NOT EXISTS (SELECT *
                FROM msdb.dbo.sysalerts
                WHERE (name = N'Sample: Full tempdb')
                   OR ((severity = 0) AND
                       (message_id = 9002) AND
                       (database_name = N'tempdb') AND
                       (event_description_keyword IS NULL) AND
                       (performance_condition IS NULL))))
	EXECUTE msdb.dbo.sp_add_alert 
		@name = N'Sample: Full tempdb',
		@message_id = 9002,
		@severity = 0,
		@enabled = 1,
		@delay_between_responses = 10,
		@database_name = N'tempdb',
		@notification_message = NULL,
		@job_name = NULL,
		@event_description_keyword = NULL,
		@include_event_description_in = 5; -- Email and NetSend
GO

IF (NOT EXISTS (SELECT *
                FROM msdb.dbo.sysalerts
                WHERE (name = N'Sample: Sev. 19 Errors')
                   OR ((severity = 19) AND
                       (message_id = 0) AND
                       (database_name IS NULL) AND
                       (event_description_keyword IS NULL) AND
                       (performance_condition IS NULL))))
	EXECUTE msdb.dbo.sp_add_alert 
		@name = N'Sample: Sev. 19 Errors',
		@message_id = 0,
		@severity = 19,
		@enabled = 1,
		@delay_between_responses = 10,
		@database_name = NULL,
		@notification_message = NULL,
		@job_name = NULL,
		@event_description_keyword = NULL,
		@include_event_description_in = 5; -- Email and NetSend
GO

IF (NOT EXISTS (SELECT *
                FROM msdb.dbo.sysalerts
                WHERE (name = N'Sample: Sev. 20 Errors')
                   OR ((severity = 20) AND
                       (message_id = 0) AND
                       (database_name IS NULL) AND
                       (event_description_keyword IS NULL) AND
                       (performance_condition IS NULL))))
	EXECUTE msdb.dbo.sp_add_alert 
		@name = N'Sample: Sev. 20 Errors',
		@message_id = 0,
		@severity = 20,
		@enabled = 1,
		@delay_between_responses = 10,
		@database_name = NULL,
		@notification_message = NULL,
		@job_name = NULL,
		@event_description_keyword = NULL,
		@include_event_description_in = 5; -- Email and NetSend
GO

IF (NOT EXISTS (SELECT *
                FROM msdb.dbo.sysalerts
                WHERE (name = N'Sample: Sev. 21 Errors')
                   OR ((severity = 21) AND
                       (message_id = 0) AND
                       (database_name IS NULL) AND
                       (event_description_keyword IS NULL) AND
                       (performance_condition IS NULL))))
	EXECUTE msdb.dbo.sp_add_alert 
		@name = N'Sample: Sev. 21 Errors',
		@message_id = 0,
		@severity = 21,
		@enabled = 1,
		@delay_between_responses = 10,
		@database_name = NULL,
		@notification_message = NULL,
		@job_name = NULL,
		@event_description_keyword = NULL,
		@include_event_description_in = 5; -- Email and NetSend
GO

IF (NOT EXISTS (SELECT *
                FROM msdb.dbo.sysalerts
                WHERE (name = N'Sample: Sev. 22 Errors')
                   OR ((severity = 22) AND
                       (message_id = 0) AND
                       (database_name IS NULL) AND
                       (event_description_keyword IS NULL) AND
                       (performance_condition IS NULL))))
	EXECUTE msdb.dbo.sp_add_alert 
		@name = N'Sample: Sev. 22 Errors',
		@message_id = 0,
		@severity = 22,
		@enabled = 1,
		@delay_between_responses = 10,
		@database_name = NULL,
		@notification_message = NULL,
		@job_name = NULL,
		@event_description_keyword = NULL,
		@include_event_description_in = 5; -- Email and NetSend
GO

IF (NOT EXISTS (SELECT *
                FROM msdb.dbo.sysalerts
                WHERE name = N'Sample: Sev. 23 Errors'
                   OR ((severity = 23) AND
                       (message_id = 0) AND
                       (database_name IS NULL) AND
                       (event_description_keyword IS NULL) AND
                       (performance_condition IS NULL))))
	EXECUTE msdb.dbo.sp_add_alert 
		@name = N'Sample: Sev. 23 Errors',
		@message_id = 0,
		@severity = 23,
		@enabled = 1,
		@delay_between_responses = 10,
		@database_name = NULL,
		@notification_message = NULL,
		@job_name = NULL,
		@event_description_keyword = NULL,
		@include_event_description_in = 5; -- Email and NetSend
GO

IF (NOT EXISTS (SELECT *
                FROM msdb.dbo.sysalerts
                WHERE (name = N'Sample: Sev. 24 Errors')
                   OR ((severity = 24) AND
                       (message_id = 0) AND
                       (database_name IS NULL) AND
                       (event_description_keyword IS NULL) AND
                       (performance_condition IS NULL))))
	EXECUTE msdb.dbo.sp_add_alert 
		@name = N'Sample: Sev. 24 Errors',
		@message_id = 0,
		@severity = 24,
		@enabled = 1,
		@delay_between_responses = 10,
		@database_name = NULL,
		@notification_message = NULL,
		@job_name = NULL,
		@event_description_keyword = NULL,
		@include_event_description_in = 5; -- Email and NetSend
GO

IF (NOT EXISTS (SELECT *
                FROM msdb.dbo.sysalerts
                WHERE (name = N'Sample: Sev. 25 Errors')
                   OR ((severity = 25) AND
                       (message_id = 0) AND
                       (database_name IS NULL) AND
                       (event_description_keyword IS NULL) AND
                       (performance_condition IS NULL))))
	EXECUTE msdb.dbo.sp_add_alert 
		@name = N'Sample: Sev. 25 Errors',
		@message_id = 0,
		@severity = 25,
		@enabled = 1,
		@delay_between_responses = 10,
		@database_name = NULL,
		@notification_message = NULL,
		@job_name = NULL,
		@event_description_keyword = NULL,
		@include_event_description_in = 5; -- Email and NetSend
GO

PRINT ''
PRINT 'Completed.'
GO

-- List the default alerts
EXECUTE msdb.dbo.sp_help_alert;
GO
